﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using quanlynhasach;

namespace quanlynhasach.Linksql
{
    class nhaphangsql
    {
        Connection db = new Connection();
        ChiTietPhieuNhap CTBH = new ChiTietPhieuNhap();
        HangHoasql hang = new HangHoasql();
        public string SearchDaTaGridPN()
        {
            int Count = 0;
            string MaPhieuNhap = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = db.getDS("Select MaPhieuNhap From PhieuNhap");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["MaPhieuNhap"].ToString() != "PN" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    MaPhieuNhap = "PN" + i;
                    break;
                }
            }
            return MaPhieuNhap;
        }


        public string SearchDaTaGridSCTPN()
        {
            int Count = 0;
            string SoCTPN = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = db.getDS("Select ID From ChiTietPhieuNhap");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["ID"].ToString() != "CTPN" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    SoCTPN = "CTPN" + i;
                    break;
                }
            }
            return SoCTPN;
        }

        

        public DataTable getCTPN(string ID)
        {
            return db.getDS("Select ID,PhieuNhap.MaPhieuNhap,PhieuNhap.NhaCungCap,PhieuNhap.MaNV,ChiTietPhieuNhap.MaSach,ChiTietPhieuNhap.SoLuong,DonVi,DonGia,ThanhTien,Thue,GiaBan from ChiTietPhieuNhap INNER JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieuNhap = PhieuNhap.MaPhieuNhap and ChiTietPhieuNhap.MaPhieuNhap = '" + ID + "'");
        }

        public DataTable getCTPN()
        {
            return db.getDS("Select ID, ChiTietPhieuNhap.MaPhieuNhap, MaSach, SoLuong,DonVi,DonGia,ThanhTien,DonVi,NhaCungCap,MaNV,Thue,GiaBan from ChiTietPhieuNhap INNER JOIN PhieuNhap on ChiTietPhieuNhap.MaPhieuNhap = PhieuNhap.MaPhieuNhap");
        }

        public string GetNameNV(string tb)
        {
                string a = "";
                DataTable dt = db.getDS("Select TenNV from NhanVien where MaNV = '" + tb + "'");
                if (dt.Rows.Count > 0)
                    a = dt.Rows[0]["TenNV"].ToString();
                else
                    a = "N/A";
                return a;
        }

        public string GetMaNV(string tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select MaNV from NhanVien where TenNV = '" + tb + "'");
            if (dt.Rows.Count > 0)
                a = dt.Rows[0]["MaNV"].ToString();
            else
                a="N/A";
            return a;
        }
        
        public string GetNameNCC(ComboBox tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select TenNCC from NhaCungCap where MaNhaCungCap = '" + tb.Text + "'");
            a = dt.Rows[0]["TenNCC"].ToString();
            return a;
        }

        public string GetNCC(string tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select MaNhaCungCap from NhaCungCap where TenNCC = '" + tb + "'");
            if (dt.Rows.Count > 0)
                a = dt.Rows[0]["MaNhaCungCap"].ToString();
            else
                a="N/A";
            return a;
        }

        public bool check(string TenSach)
        {
            if (db.getDS("Select * from Sach where TenSach ='" + TenSach + "'").Rows.Count <= 0)
            {
                return false;
            }
            return true;
        }

        public string GetMoney(string MaPN)
        {
            string a = "";
            DataTable dt = db.getDS("Select TongTien from PhieuNhap where MaPhieuNhap = '" + MaPN + "'");
            a = dt.Rows[0]["TongTien"].ToString();
            return a;
        }

        public bool CheckCTPN(string SoCTPN, string MaPhieuNhap)
        {
            if (db.getDS("Select * from ChiTietPhieuNhap where ID ='" + SoCTPN + "' and MaPhieuNhap = '" + MaPhieuNhap + "'").Rows.Count > 0)
                return true;
            return false;
        }

        public bool XoaCTPN(string ID)
        {
            string[] param = { "@ID" };
            object[] value = { ID, };
            string query = "Delete from ChiTietPhieuNhap where ID=@ID";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool XoaPN(string MaPhieuNhap)
        {
            string[] param = { "@MaPhieuNhap" };
            object[] value = { MaPhieuNhap };
            string query = "Delete from PhieuNhap where MaPhieuNhap=@MaPhieuNhap";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool SuaCTPN(ChiTietPhieuNhap bh)
        {
            string[] param = { "@ID", "@MaSach", "@SoLuong", "@DonGia", "@DonVi", "@ThanhTien", "@Thue", "@GiaBan"};
            object[] value = { bh.SoCTPN, bh.MaSachPN, bh.SoLuongPN, bh.DonGiaPN, bh.DonViPN, bh.ThanhTienPN, bh.Thue, bh.GiaBan };
            string query = "Update ChiTietPhieuNhap"
                           + " set  MaSach=@MaSach,SoLuong=@Soluong,DonGia = @DonGia,"
                           + " DonVi=@DonVi,ThanhTien = @ThanhTien, Thue=@Thue, GiaBan=@GiaBan"
                           + " where ID=@ID";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool ThemPN(HoaDonNhap hdn)
        {
            string[] param ={"@MaPN","@MaNV","@TongTien","@Ngay",
                                "@NhaCungCap"};
            object[] value = { hdn.MaHDN, hdn.MaNVHDN, hdn.TongTienHDN, hdn.NgayHDN, hdn.MaNhaCungCap };
            string query = "Insert into PhieuNhap"
                           + "(MaPhieuNhap,MaNV,TongTien,Ngay,NhaCungCap)"
                           + " values (@MaPN,@MaNV,@TongTien,@Ngay,@NhaCungCap)";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool ThemCTPN(ChiTietPhieuNhap pn)
        {
            string[] param ={"@ID","@MaPN","@MaSach","@SoLuong","@DonGia",
                                "@ThanhTien","@DonVi", "@Thue", "@GiaBan"};
            object[] value = { pn.SoCTPN, pn.MaPN, pn.MaSachPN, pn.SoLuongPN, pn.DonGiaPN, pn.ThanhTienPN, pn.DonViPN, pn.Thue, pn.GiaBan };
            string query = "Insert into ChiTietPhieuNhap"
                           + "(ID,MaPhieuNhap,MaSach,SoLuong,DonGia,ThanhTien,DonVi,Thue,GiaBan)"
                           + " values (@ID,@MaPN,@MaSach,@SoLuong,@DonGia,@ThanhTien,@DonVi,@Thue,@GiaBan)";
            return db.ExecuteNonQueryPara(query, param, value);
        }
    }
}
